Create, Delete, and Alter a Stored Procedure

Create, Alter, and Delete a Stored Procedure#

To create a stored procedure we use the CREATE PROCEDURE statement. CREATE PROCEDURE keywords are followed by the procedure name. We can also specify parameters to our procedure as a comma separated list withing parenthesis after the procedure name. The body of the stored procedure is enclosed with in the BEGIN and END keywords.

In order to delete a stored procedure, the ALTER ROUTINE privilege is a must. The DROP PROCEDURE statement deletes a stored procedure from the database. It can be used with the optional IF EXISTS clause.

Syntax#

DELIMITER **

CREATE PROCEDURE procedure_name( parameter_list )

BEGIN

procedure_body

END**

DELIMITER ;

DROP PROCEDURE [IF EXISTS] procedure_name;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy-paste the command ./DataJek/Lessons/51lesson.sh and wait for the mysql prompt to start-up.

1. We can create a stored procedure ShowActors that displays all the actors in our database as follows:

   DELIMITER **
   CREATE PROCEDURE ShowActors()
   BEGIN
    SELECT *  FROM Actors;
   END **
   DELIMITER ;
   

   A stored procedure named ShowActors is created which, when called, will display all the actors. We have used the DELIMITER command in line 1. By default semicolon (;) is used to separate two statements. Since a stored procedure can have multiple statements that end with semicolon character, it will not be considered as a single statement. The DELIMITER keyword is used to redefine the delimiter to ** so that we can pass the whole stored procedure to the server as a single statement. We use our redefined delimiter in line 5 to signal the end of the stored procedure. Then the delimiter is set back to semicolon in the last line.

   The body of our stored procedure consists of a simple SELECT statement.

2. The next step is to execute our stored procedure. The CALL statement is used to invoke a stored procedure as follows:

   CALL ShowActors();
   

   Executing the above statement is the same as executing a query. The image shows the results of ShowActors procedure:

3. To view the stored procedures in a database, we will use the SHOW PROCEDURE STATUS statement as follows:

   SHOW PROCEDURE STATUS;
   

   The output of the above command is copious and captured in the widget below:

4. A WHERE clause can be used with the above statement to view the stored procedures of any database by specifying the name of the database. For example:

   SHOW PROCEDURE STATUS WHERE db = 
   'MovieIndustry';
   

   The information_schema database contains the data about all the stored procedures in all the databases. We can query the routines table in this database as follows:

   SELECT routine_name
   FROM information_schema.routines
   WHERE routine_type = 'PROCEDURE'
   AND routine_schema = 'sys';
   

   the above query lists 26 procedures in the sys database.

5. To delete the stored procedure we just created execute the following query:

   DROP PROCEDURE IF EXISTS ShowActors;
   

   The IF EXISTS clause in this statement is optional. It is used to avoid an error message in case we attempt to delete a stored procedure that does not exist. A warning is issued instead.

   As it can be seen, ShowActors() has been successfully deleted from our database.

6. It is possible to make changes to a stored procedure. However, there are no MySQL statements that can directly modify the parameter list or the body of the stored procedure. To make changes to a stored procedure, the only way is to delete the procedure and then re-create it.